package com.hibernate.hql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.jdbc.Work;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

@SuppressWarnings("unchecked")
public class MainDriver {

	private Configuration cfg;
	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;

	public int add(int a, int b) {
		return a + b;
	}

	
	/**
	 * @Description: 连接
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void innerJoin(){
		openSession();
		//最简单的写法，内连接
		String hql2 = "from Student s inner join s.grade g";
		//
		String hql = "select g.name,g.desci,s.name from Student s,Grade g where g=s.grade";
		
		//右外连接
		String hql3 = "select g.name,g.desci,s.name from Student s left join s.grade g";
		Query q = session.createQuery(hql2);
		List list = q.list();
		Iterator iterator = list.iterator();
		while (iterator.hasNext()) {// 记录数
			Object[] next = (Object[]) iterator.next();
			System.out.println(next[0] + "," + next[1]+","+next[2]);// 0,1代表有几个字段，
		}
		closeSession();
	}
	
	/**
	 * 分页
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void cpage() {
		openSession();
		int pageSize = 3;

		// 1.查出总数量
		Query q = session.createQuery("select count(*) from User");
		long count2 = (Long) q.uniqueResult();

		int count = (int) count2;
		// 2.求页数：
		int shul = count % pageSize == 0 ? count / pageSize : count / pageSize + 1;

		Query q2= session.createQuery("from User");
		for(int i=0;i<shul;i++){
			//设置开始位置
			q2.setFirstResult(i * pageSize);
			//设置每页的数量
			q2.setMaxResults(pageSize);
			List<User> list = q2.list();
			for(User u : list){
				System.out.println(u);
			}
			System.out.println("----------------");
		}
		closeSession();
	}

	/**
	 * @Description: 分组
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void groupBy() {
		openSession();
		String hql = "select max(age) as maxAge,sex from User group by sex";
		Query q = session.createQuery(hql);
		List ages = q.list();
		Iterator iterator = ages.iterator();
		while (iterator.hasNext()) {// 记录数
			Object[] next = (Object[]) iterator.next();
			System.out.println(next[0] + "," + next[1]);// 0,1代表有几个字段，
		}

		closeSession();
	}

	/**
	 * 统计函数 
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void function() {
		openSession();
		String hql = "select max(age),avg(age),min(age),sum(age) from User";
		Query q = session.createQuery(hql);
		Object[] objs =  (Object[]) q.uniqueResult();
		for(Object o : objs){
			System.out.println(o);
		}
		closeSession();
	}

	/**
	 * 和SQL排序几乎一样，一样可以指定多个排序条件，如order by age desc,name asc
	 */
	@Test
	public void orderBy() {
		openSession();
		String hql = "from User order by age desc,sex asc";
		Query q = session.createQuery(hql);
		
		List<User> list = q.list();
		System.out.println(list);
		closeSession();
	}

	/**
	 * @Description:修改
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void update() {
		openSession();
		String name = "王二麻子";
		String hql = "update User set age=250 where name='" + name + "'";
		Query qry = session.createQuery(hql);
		qry.executeUpdate();

		closeSession();
	}

	/**
	 * @Description: 带参数的查询
	 	有三种方式：
	 	1.拼接字符串，和SQL中类似
	 	2.使用占位符?，和SQL中类似
	 	3.使用命名参数，
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void qryWithCondition(){
		openSession();
		int id = 1;
		int age = 10000;
		
		//1.拼接字符串，缺点：SQL注入，书写麻烦
		String hql = "from User where id='"+id+"' and age="+age;
		Query qry1 = session.createQuery(hql);
		User u = (User) qry1.uniqueResult();
		System.out.println(u);
		
		//2.使用占位符，缺点：如果修改了参数的位置，需要改变传入值的设置
		String hql2 = "from User where id=? and age=?";
		Query qry2 = session.createQuery(hql2);
		qry2.setInteger(0, id);
		qry2.setInteger(1, age);
		//可以使用setParameter设置任意类型参数
		qry2.setParameter(1, age);
		User u2 = (User) qry2.uniqueResult();
		System.out.println(u2);
		
		//3.使用命名参数,使用:参数名的方式来占位
		String hql3 = "from User where id=:id and age=:age";
		Query qry3 = session.createQuery(hql3);
		qry3.setParameter("id", id);
		qry3.setParameter("age",age);
		
		User u3 = (User) qry3.uniqueResult();
		System.out.println(u3);
		
		closeSession();
	}
	
	
	/**
	 * @Description: 查询部分属性
	 	返回的结果是中每一条是一个对象数组，代表多个属性，使用List<Object[]>接受
	 	如果查询的只有一个字段，可以用List<Object>来接受
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void qryPartProperties(){
		openSession();
		
		Query qry = session.createQuery("select name,age from User");
		List<Object[]> list = qry.list();//得到的是一个对象数组，
//		List<Object> list = qry.list();
		System.out.println(list);
		
		Query qry2 = session.createQuery("select new User(name,age) from User");
		List<User> list2 = qry2.list();
		System.out.println(list2);

		closeSession();
	}

	
	/**
	 * @Description: 最简单的查询
	 * @输入和输出示例：
	 * @思路：
	 */
	@Test
	public void qryAll(){
		openSession();
		
		Query qry = session.createQuery("from User");
		List<User> list = qry.list();
		System.out.println(list);
		
		closeSession();
	}
	
	@Test
	public void addData() {
		openSession();
		
		session.save(new User("张三", "男", 10000));
		session.save(new User("李四", "女", 26));
		session.save(new User("王二麻子", "男", 40));
		session.save(new User("大圣", "男", 500));
		session.save(new User("石头", "男", 30));
		
		Student stu1 = new Student("vn", "男");
		Student stu2 = new Student("石头人", "男");
		Student stu3 = new Student("亚索", "男");
		Grade g = new Grade("德玛西亚", "AAA");
		stu1.setGrade(g);
		stu2.setGrade(g);
		stu3.setGrade(g);
		
		session.save(g);
		session.save(stu1);
		session.save(stu2);
		session.save(stu3);
		
		closeSession();
	}
	
	/**
	 * @Description: 使用存储过程
	 * @输入和输出示例：
	 * @思路：
	 */
	public void storedProcedure(){
		openSession();
		session.doWork(new Work() {
			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement prepareCall = connection.prepareCall("");
				prepareCall.execute();
			}
		});
		closeSession();
	}

	private void openSession() {
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}

	private void closeSession() {
		transaction.commit();
		session.close();
	}

	@Before
	public void init() {
		cfg = new Configuration().configure();
		sessionFactory = cfg.buildSessionFactory(
				new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry());
		System.out.println("创建SessionFactory");
	}

	@After
	public void destory() {
		sessionFactory.close();
		System.out.println("关闭SessionFactory");
	}

}
